﻿using Blog190311.Models;
using Blog190311.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Blog190311.Areas.Back.Models.DAL
{
    public class ArticleService
    {
        /// <summary>
        /// 分页多条件查询
        /// </summary>
        /// <param name="limit"></param>
        /// <param name="page"></param>
        /// <param name="Title"></param>
        /// <param name="Type"></param>
        /// <param name="IsPub"></param>
        /// <param name="Tags"></param>
        /// <returns></returns>
        public List<Article> GetArticles(int limit, int page, string Title, string Type, string IsPub, string Tags)
        {
            List<Article> lists = new List<Article>();//定义数组
            string sqlwhere = "";//定义空的sql语句

            if (!string.IsNullOrWhiteSpace(Title)) //判断标题
            {
                sqlwhere += string.Format("and Title like '%{0}%'", Title);
            }
            if (!string.IsNullOrWhiteSpace(Type))//判断类别
            {
                sqlwhere += string.Format("and Type like '%{0}%'", Type);
            }
            if (!string.IsNullOrWhiteSpace(IsPub))//判断是否发布
            {
                sqlwhere += string.Format("and IsPub like '%{0}%'", IsPub);
            }
            if (!string.IsNullOrWhiteSpace(Tags))//判断标签
            {
                sqlwhere += string.Format("and Tags like '%{0}%'", Tags);
            }
            string sql = string.Format("select top({0}) * from Articles where Id not in(select top({1}) Id from Articles where ShowType='文章') and ShowType='文章'", limit, limit * (page - 1));
            sql += sqlwhere;//拼接sql语句
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                lists.Add(new Article
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    Title = dr["Title"].ToString(),
                    ReadTimes = Convert.ToInt32(dr["ReadTimes"]),
                    Tags = dr["Tags"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    MainPicUrl = dr["MainPicUrl"].ToString(),
                    Source = dr["Source"].ToString(),
                    Type = dr["Type"].ToString(),
                    CreateTime = dr["CreateTime"].ToString(),
                    PubTime = dr["PubTime"].ToString(),
                    IsPub = dr["IsPub"].ToString(),
                    ShowType = dr["ShowType"].ToString(),

                });
            }
            return lists;
        }

        /// <summary>
        /// 获取满足条件的数据条数
        /// </summary>
        /// <param name="Title"></param>
        /// <param name="Type"></param>
        /// <param name="IsPub"></param>
        /// <param name="Tags"></param>
        /// <returns></returns>
        public int GetCount(string Title, string Type, string IsPub, string Tags)
        {
            string sqlwhere = "";
            if (!string.IsNullOrWhiteSpace(Title)) //判断标题
            {
                sqlwhere = string.Format("and Title like '%{0}%'", Title);
            }
            if (!string.IsNullOrWhiteSpace(Type))//判断类别
            {
                sqlwhere = string.Format("and Type like '%{0}%'", Type);
            }
            if (!string.IsNullOrWhiteSpace(IsPub))//判断是否发布
            {
                sqlwhere = string.Format("and IsPub like '%{0}%'", IsPub);
            }
            if (!string.IsNullOrWhiteSpace(Tags))//判断标签
            {
                sqlwhere = string.Format("and Tags like '%{0}%'", Tags);
            }
            string sql = "select COUNT(*)from Articles where ShowType='文章'";
            sql += sqlwhere;
            object re = DBHelper.ExecuteScalar(sql);
            if (re != null)
            {
                return Convert.ToInt32(re);
            }
            return 0;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="articles"></param>
        /// <returns></returns>
        public int Addarticle(Article articles)
        {
            string sql = string.Format("insert into Articles (Title,ReadTimes,Tags,SimpleInfo,Cont,MainPicUrl,Source,Type,CreateTime,PubTime,IsPub,ShowType)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')", articles.Title, articles.ReadTimes, articles.Tags, articles.SimpleInfo, articles.Cont, articles.MainPicUrl, articles.Source, articles.Type, articles.CreateTime, articles.PubTime, articles.IsPub, articles.ShowType);
            int num = DBHelper.ExecuteNonQuery(sql);
            return num;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <returns></returns>
        public int UpdateArticle(Article articl)
        {
            string sqlyuju = string.Format("update Articles set Title='{0}',ReadTimes='{1}',Tags='{2}',SimpleInfo='{3}',Cont='{4}',MainPicUrl='{5}',Source='{6}',Type='{7}',CreateTime='{8}',PubTime='{9}',IsPub='{10}',ShowType='{11}' where Id={12}", articl.Title, articl.ReadTimes, articl.Tags, articl.SimpleInfo, articl.Cont, articl.MainPicUrl, articl.Source, articl.Type, articl.CreateTime, articl.PubTime, articl.IsPub, articl.ShowType, articl.Id);
            int num = DBHelper.ExecuteNonQuery(sqlyuju);
            return num;
        }

        /// <summary>
        /// 通过Id获取文章单条数据
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Article GetArticleId(int Id)
        {
            string sqlyuju = string.Format("select *from Articles where Id={0}", Id);
            SqlDataReader re = DBHelper.ExecuteReader(sqlyuju);
            while (re.Read())
            {
                Article article = new Article()
                {
                    Id = Convert.ToInt32(re["Id"]),
                    Title = re["Title"].ToString(),
                    ReadTimes = Convert.ToInt32(re["ReadTimes"]),
                    Tags = re["Tags"].ToString(),
                    SimpleInfo = re["SimpleInfo"].ToString(),
                    Cont = re["Cont"].ToString(),
                    MainPicUrl = re["MainPicUrl"].ToString(),
                    Source = re["Source"].ToString(),
                    Type = re["Type"].ToString(),
                    CreateTime = re["CreateTime"].ToString(),
                    PubTime = re["PubTime"].ToString(),
                    IsPub = re["IsPub"].ToString(),
                    ShowType = re["ShowType"].ToString()
                };
                return article;
            }
            return null;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delarticle(int id)
        {
            string sql = string.Format("delete from Articles where Id={0}", id);
            int num = DBHelper.ExecuteNonQuery(sql);
            return num;
        }

        /// <summary>
        /// 发布
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Fabu(int id)
        {
            string sql = string.Format("update Articles set IsPub='是' where Id={0}", id);
            int num = DBHelper.ExecuteNonQuery(sql);
            return num;
        }
    }
}